
/*
    Save or update test_release table row based on TEST_REL_ID.
    Balaram, April 17, 2005
*/

alter proc sp_save_or_update_test_release (

@test_rel_id int,
@test_rel_number varchar(50),
@customer_id int,
@vendor_id int,
@process_step_id int,
@blank_part_number varchar(50),
@blank_part_desc varchar(1000),
@prog_part_number varchar(50),
@prog_part_name varchar(50),
@part_status varchar(20),
@revision varchar(10),
@user_po varchar(50),
@cust_po varchar(50),
@comm_inv_price varchar(50),
@alg_name varchar(50),
@run_as_replace bit,
@job_number varchar(50),
@contact varchar(50),
@issue_date datetime,
@approved_date datetime,
@approved_by varchar(20),
@released_date datetime,
@released_by varchar(20),
@last_modified_date datetime,
@last_modified_by varchar(20),
@orderd_qty int,
@completion_target_date datetime,
@special_instr varchar(1000),
@comments varchar(1000),
@shipping_address varchar(500),
@carrier varchar(50),
@carrier_account varchar(50),
@status varchar(20),
@created_test_rel_id int out )

as
begin
	declare @row_count int
	select @row_count = count(*) from test_release where test_rel_id = @test_rel_id

	if @row_count = 0 -- insert
	begin
		insert into test_release ( TEST_REL_NUMB, CUSTOMERID, VENDORID, PSTEPID, BLANKPARTNUMB, 
		BLANKPARTDESC, PRGPARTNUMB, PRGPARTNAME, PARTSTATUS, REVISION, USERPO, CUSTPO, COMMINVPRICE, 
		ALGNAME, RUNASREPLACE, JOBNUMBER, CONTACT, ISSUEDATE, APPROVEDDATE, APPROVEDBY, RELEASEDDATE, 
		RELEASEDBY, LASTMODIFIEDDATE, LASTMODIFIEDBY, ORDERQTY, SHIPPING, COMPLETEDTGTDATE, SPINSTRUCTIONS, 
		COMMENTS, CARRIER, CARRIERACCOUNT, STATUS  )
		
		values( @test_rel_number, @customer_id, @vendor_id, @process_step_id, @blank_part_number, 
		@blank_part_desc, @prog_part_number, @prog_part_name, @part_status, @revision, @user_po, 
		@cust_po, @comm_inv_price, @alg_name, @run_as_replace, @job_number, @contact, @issue_date,
		@approved_date, @approved_by, @released_date, @released_by, @last_modified_date, 
		@last_modified_by, @orderd_qty, @shipping_address, @completion_target_date, @special_instr, 
		@comments, @carrier, @carrier_account, @status )

		select @created_test_rel_id = @@identity
	end

	else -- update
	begin
		UPDATE TEST_RELEASE
		set TEST_REL_NUMB = @test_rel_number, CUSTOMERID = @customer_id, VENDORID = @vendor_id, PSTEPID = @process_step_id,
		BLANKPARTNUMB = @blank_part_number, BLANKPARTDESC = @blank_part_desc, PRGPARTNUMB = @prog_part_number, 
		PRGPARTNAME = @prog_part_name, PARTSTATUS = @part_status, REVISION = @revision, USERPO = @user_po, 
		CUSTPO = @cust_po, COMMINVPRICE = @comm_inv_price, ALGNAME = @alg_name, RUNASREPLACE = @run_as_replace, 
		JOBNUMBER = @job_number, CONTACT = @contact, ISSUEDATE = @issue_date, APPROVEDDATE = @approved_date, 
		APPROVEDBY = @approved_by, RELEASEDDATE = @released_date, RELEASEDBY = @released_by, LASTMODIFIEDDATE = @last_modified_date, 
		LASTMODIFIEDBY = @last_modified_by, ORDERQTY = @orderd_qty, COMPLETEDTGTDATE = @completion_target_date, 
		SPINSTRUCTIONS = @special_instr, COMMENTS = @comments, SHIPPING = @shipping_address, CARRIER = @carrier, 
		CARRIERACCOUNT = @carrier_account, STATUS = @status
		WHERE TEST_REL_ID = @test_rel_id

		select @created_test_rel_id = @test_rel_id
	end
end